# akshare_finance/exporter.py
from typing import Dict, Tuple, List, Any
from datetime import datetime
from pathlib import Path
import pandas as pd
import os
import re
from openpyxl.styles import Font

from akshare_finance.models import Sector


class ExcelExporter:
    def __init__(
        self,
        output_dir: str = "output",
        time_periods: List[str] = None,
        top_n: int = 20,
    ) -> None:
        self.output_dir = Path(output_dir)
        self.output_dir.mkdir(parents=True, exist_ok=True)
        self.time_periods = time_periods or []
        self.top_n = top_n

    # ---------- 辅助：数值转换 ----------
    @staticmethod
    def _to_numeric(series: pd.Series) -> pd.Series:
        return pd.to_numeric(
            series.astype(str).str.replace("%", "", regex=False), errors="coerce"
        )

    # ---------- 辅助：应用中英文字体 ----------
    def _apply_fonts_to_sheet(self, sheet) -> None:
        """
        将含中文字符的单元格设置为等线，其余设置为 Arial。
        """
        try:
            chinese_re = getattr(self, "_chinese_re", None)
            if chinese_re is None:
                chinese_re = re.compile(r"[\u4e00-\u9fff]")
                self._chinese_re = chinese_re
            for row in sheet.iter_rows():
                for cell in row:
                    v = cell.value
                    if v is None:
                        continue
                    use_dengxian = (
                        isinstance(v, str) and chinese_re.search(v) is not None
                    )
                    target_name = "DengXian" if use_dengxian else "Arial"
                    f = cell.font
                    # 仅当需要变更时才设置，避免不必要写入
                    if f is None or f.name != target_name:
                        cell.font = Font(
                            name=target_name,
                            size=(f.size if f and f.size else 11),
                            bold=(f.bold if f else False),
                            italic=(f.italic if f else False),
                            vertAlign=(f.vertAlign if f else None),
                            underline=(f.underline if f else None),
                            strike=(f.strike if f else False),
                            color=(f.color if f else None),
                        )
        except Exception:
            # 字体应用失败不影响导出
            pass

    def _extract_latest_values(self, financial_df: pd.DataFrame) -> pd.DataFrame:
        """从单个公司的财务表提取各指标最近期的数值。返回列：指标, 期间, 数值"""
        if financial_df is None or financial_df.empty:
            return pd.DataFrame(columns=["指标", "期间", "数值"])
        rows: List[Dict[str, Any]] = []
        for _, r in financial_df.iterrows():
            metric = r.get("指标")
            if pd.isna(metric):
                continue
            latest_period = next(
                (p for p in self.time_periods if p in r.index and pd.notna(r[p])), None
            )
            if latest_period is None:
                continue
            val = self._to_numeric(pd.Series([r[latest_period]])).iloc[0]
            if pd.isna(val):
                continue
            rows.append({"指标": metric, "期间": latest_period, "数值": float(val)})
        return pd.DataFrame(rows)

    def _extract_latest_growth(self, financial_df: pd.DataFrame) -> pd.DataFrame:
        """从单个公司的财务表计算各指标最近两期增长率(%)。返回列：指标, 最近期, 上期, 最新值, 上期值, 增长率(%)"""
        if financial_df is None or financial_df.empty:
            return pd.DataFrame(
                columns=["指标", "最近期", "上期", "最新值", "上期值", "增长率(%)"]
            )
        rows: List[Dict[str, Any]] = []
        for _, r in financial_df.iterrows():
            metric = r.get("指标")
            if pd.isna(metric):
                continue
            periods_available = [
                p for p in self.time_periods if p in r.index and pd.notna(r[p])
            ]
            if len(periods_available) < 2:
                continue
            p_new, p_old = periods_available[0], periods_available[1]
            v_new = self._to_numeric(pd.Series([r[p_new]])).iloc[0]
            v_old = self._to_numeric(pd.Series([r[p_old]])).iloc[0]
            if pd.isna(v_new) or pd.isna(v_old) or float(v_old) == 0.0:
                continue
            growth = (float(v_new) - float(v_old)) / abs(float(v_old)) * 100.0
            rows.append(
                {
                    "指标": metric,
                    "最近期": p_new,
                    "上期": p_old,
                    "最新值": round(float(v_new), 4),
                    "上期值": round(float(v_old), 4),
                    "增长率(%)": round(growth, 4),
                }
            )
        return pd.DataFrame(rows)

    def export_sector_analysis(
        self,
        sector,
        companies_data: Dict[
            str, Tuple[pd.DataFrame, pd.DataFrame, pd.DataFrame, pd.DataFrame]
        ],
        sector_statistics: pd.DataFrame,
    ) -> str:

        ts = datetime.now().strftime("%Y%m%d_%H%M%S")
        filename = f"{sector.name}_{ts}.xlsx"
        fp = self.output_dir / filename
        with pd.ExcelWriter(fp, engine="openpyxl") as writer:
            if not sector_statistics.empty:
                sector_statistics.to_excel(writer, sheet_name="板块统计", index=False)
            for cname, (
                financial_df,
                business_df,
                dupont_basic_df,
                dupont_adv_df,
            ) in companies_data.items():
                sheet = cname[:31]
                startrow = 0
                info_df = pd.DataFrame(
                    {
                        "信息类型": ["公司名称", "股票代码"],
                        "详细信息": [
                            cname,
                            getattr(
                                next(
                                    (c for c in sector.companies if c.name == cname),
                                    None,
                                ),
                                "symbol",
                                "",
                            ),
                        ],
                    }
                )
                info_df.to_excel(
                    writer, sheet_name=sheet, index=False, startrow=startrow
                )
                startrow += len(info_df) + 2
                if financial_df is not None and not financial_df.empty:
                    financial_df.to_excel(
                        writer, sheet_name=sheet, index=False, startrow=startrow
                    )
                    startrow += len(financial_df) + 2
                if business_df is not None and not business_df.empty:
                    business_df.to_excel(
                        writer, sheet_name=sheet, index=False, startrow=startrow
                    )
                    startrow += len(business_df) + 2

                # 基础杜邦分析
                if dupont_basic_df is not None and not dupont_basic_df.empty:
                    # 若索引为报告期，将其写为首列
                    db = dupont_basic_df.reset_index()
                    db.rename(columns={db.columns[0]: "报告期"}, inplace=True)
                    db.to_excel(
                        writer,
                        sheet_name=sheet,
                        index=False,
                        startrow=startrow,
                    )
                    startrow += len(db) + 2

                # 改进杜邦分析
                if dupont_adv_df is not None and not dupont_adv_df.empty:
                    da = dupont_adv_df.reset_index()
                    # 某些情况下没有 index name，保持统一
                    if da.columns[0] != "报告期":
                        da.rename(columns={da.columns[0]: "报告期"}, inplace=True)
                    da.to_excel(
                        writer,
                        sheet_name=sheet,
                        index=False,
                        startrow=startrow,
                    )
                    startrow += len(da) + 2
        return str(fp)

    def export_all_sectors(
        self,
        all_sectors_data: Dict[
            str,
            Tuple[
                Sector,
                Dict[
                    str, Tuple[pd.DataFrame, pd.DataFrame, pd.DataFrame, pd.DataFrame]
                ],
                pd.DataFrame,
            ],
        ],
    ) -> str:
        """
        导出所有板块的分析结果到一个Excel文件：
        - 总览
        - 所有板块统计汇总
        - 各板块统计明细（每个板块一个 *_统计）
        - 所有公司汇总（将财务与主营业务合并到同一张Sheet）
        """

        def _adjust_sheet(sheet):
            """冻结首行 + 自动列宽"""
            try:
                sheet.freeze_panes = "A2"  # 冻结第一行
                for col in sheet.columns:
                    max_len = max(
                        (len(str(cell.value)) if cell.value is not None else 0)
                        for cell in col
                    )
                    col_letter = col[0].column_letter
                    sheet.column_dimensions[col_letter].width = min(max_len + 2, 60)
            except Exception:
                pass

        # 生成文件名
        timestamp: str = datetime.now().strftime("%Y%m%d_%H%M%S")
        filename: str = f"全部板块分析报告_{timestamp}.xlsx"
        filepath: str = os.path.join(self.output_dir, filename)

        with pd.ExcelWriter(filepath, engine="openpyxl") as writer:
            # Sheet 名称去重工具
            used_sheet_names: set[str] = set()

            def _unique_sheet_name(base: str) -> str:
                name = base[:31] if base else "Sheet"
                if name not in used_sheet_names and (
                    not hasattr(writer, "book")
                    or name not in getattr(writer, "book").sheetnames
                ):
                    used_sheet_names.add(name)
                    return name
                # 追加序号直到唯一
                idx = 1
                while True:
                    suffix = f"_{idx}"
                    candidate = (
                        (base[: 31 - len(suffix)] + suffix)
                        if len(base) + len(suffix) > 31
                        else base + suffix
                    )
                    if candidate not in used_sheet_names and (
                        not hasattr(writer, "book")
                        or candidate not in getattr(writer, "book").sheetnames
                    ):
                        used_sheet_names.add(candidate)
                        return candidate
                    idx += 1

            # 1) 总览
            overview_rows: List[Dict[str, Any]] = []
            for sector_name, (
                sector,
                companies_data,
                statistics,
            ) in all_sectors_data.items():
                total = len(sector.companies)
                valid = sum(
                    1
                    for _, (fin_df, _, _, _) in companies_data.items()
                    if fin_df is not None and not fin_df.empty
                )
                overview_rows.append(
                    {
                        "板块名称": sector_name,
                        "公司总数": total,
                        "有效数据公司数": valid,
                        "数据完整率(%)": (
                            round(valid / total * 100, 1) if total > 0 else 0.0
                        ),
                    }
                )
            pd.DataFrame(overview_rows).to_excel(writer, sheet_name="总览", index=False)
            sheet_overview = writer.book["总览"]
            _adjust_sheet(sheet_overview)
            self._apply_fonts_to_sheet(sheet_overview)

            # 2) 简化的板块对比表（只显示公司数量和数据完整率）
            comparison_rows = []
            for sector_name, (sector, companies_data, _) in all_sectors_data.items():
                valid_companies = sum(
                    1 for _, (fin_df, _, dupont_basic, dupont_adv) in companies_data.items()
                    if (fin_df is not None and not fin_df.empty) or
                       (dupont_basic is not None and not dupont_basic.empty) or
                       (dupont_adv is not None and not dupont_adv.empty)
                )
                
                comparison_rows.append({
                    "板块名称": sector_name,
                    "公司总数": len(sector.companies),
                    "有数据公司数": valid_companies,
                    "财务数据完整率(%)": round(valid_companies / len(sector.companies) * 100, 1) if len(sector.companies) > 0 else 0,
                })
            
            comparison_df = pd.DataFrame(comparison_rows)
            comparison_df.to_excel(writer, sheet_name="板块对比", index=False)
            sh = writer.book["板块对比"]
            _adjust_sheet(sh)
            self._apply_fonts_to_sheet(sh)

            # 3) 简化的板块汇总（移除复杂统计）
            # 为了精简代码，移除复杂的板块统计功能，因为数据格式转换存在问题

            # 3.1) 每公司明细（含财务/主营/杜邦基础/杜邦改进），与 export_sector_analysis 风格一致
            for sector_name, (sector, companies_data, _) in all_sectors_data.items():
                for company in sector.companies:
                    if company.name not in companies_data:
                        continue
                    financial_df, business_df, dupont_basic_df, dupont_adv_df = (
                        companies_data[company.name]
                    )
                    # 若四者均为空，则跳过，避免无意义 Sheet
                    if (
                        (financial_df is None or financial_df.empty)
                        and (business_df is None or business_df.empty)
                        and (dupont_basic_df is None or dupont_basic_df.empty)
                        and (dupont_adv_df is None or dupont_adv_df.empty)
                    ):
                        continue

                    base_name = f"{company.name}-{company.symbol}"
                    sheet = _unique_sheet_name(base_name)
                    startrow = 0

                    info_df = pd.DataFrame(
                        {
                            "信息类型": ["板块名称", "公司名称", "股票代码"],
                            "详细信息": [sector_name, company.name, company.symbol],
                        }
                    )
                    info_df.to_excel(
                        writer, sheet_name=sheet, index=False, startrow=startrow
                    )
                    startrow += len(info_df) + 2

                    if financial_df is not None and not financial_df.empty:
                        financial_df.to_excel(
                            writer, sheet_name=sheet, index=False, startrow=startrow
                        )
                        startrow += len(financial_df) + 2

                    if business_df is not None and not business_df.empty:
                        business_df.to_excel(
                            writer, sheet_name=sheet, index=False, startrow=startrow
                        )
                        startrow += len(business_df) + 2

                    if dupont_basic_df is not None and not dupont_basic_df.empty:
                        db = dupont_basic_df.reset_index()
                        if db.columns[0] != "报告期":
                            db.rename(columns={db.columns[0]: "报告期"}, inplace=True)
                        db.to_excel(
                            writer, sheet_name=sheet, index=False, startrow=startrow
                        )
                        startrow += len(db) + 2

                    if dupont_adv_df is not None and not dupont_adv_df.empty:
                        da = dupont_adv_df.reset_index()
                        if da.columns[0] != "报告期":
                            da.rename(columns={da.columns[0]: "报告期"}, inplace=True)
                        da.to_excel(
                            writer, sheet_name=sheet, index=False, startrow=startrow
                        )
                        startrow += len(da) + 2

                    sh = writer.book[sheet]
                    self._apply_fonts_to_sheet(sh)

            # 4 & 5) 合并为一张「所有公司汇总」Sheet（财务 + 主营业务）
            financial_rows: List[Dict[str, Any]] = []
            business_rows: List[Dict[str, Any]] = []
            # 排行榜收集
            latest_ranking_rows: List[Dict[str, Any]] = []
            growth_ranking_rows: List[Dict[str, Any]] = []

            # 4.1 收集财务（横向列：指标_期间）
            for sector_name, (sector, companies_data, _) in all_sectors_data.items():
                for company in sector.companies:
                    if company.name not in companies_data:
                        continue
                    financial_df, _, _, _ = companies_data[company.name]
                    if financial_df is None or financial_df.empty:
                        continue

                    row: Dict[str, Any] = {
                        "板块名称": sector_name,
                        "公司名称": company.name,
                        "股票代码": company.symbol,
                    }
                    # 将每个指标在每个期间的值拼为列：<指标>_<期间>
                    for _, r in financial_df.iterrows():
                        metric = r.get("指标")
                        if metric is None or (
                            isinstance(metric, float) and pd.isna(metric)
                        ):
                            continue
                        for period in self.time_periods:
                            if period in r.index:
                                val = r.get(period, None)
                                if pd.notna(val):
                                    row[f"{metric}_{period}"] = val
                    financial_rows.append(row)

                    # 排行榜：最新值、增长率（逐公司累积）
                    latest_df = self._extract_latest_values(financial_df)
                    if not latest_df.empty:
                        latest_df = latest_df.assign(
                            板块名称=sector_name,
                            公司名称=company.name,
                            股票代码=company.symbol,
                        )
                        for _, r2 in latest_df.iterrows():
                            latest_ranking_rows.append(
                                {
                                    "指标": r2["指标"],
                                    "期间": r2["期间"],
                                    "板块名称": sector_name,
                                    "公司名称": company.name,
                                    "股票代码": company.symbol,
                                    "数值": r2["数值"],
                                }
                            )

                    growth_df = self._extract_latest_growth(financial_df)
                    if not growth_df.empty:
                        growth_df = growth_df.assign(
                            板块名称=sector_name,
                            公司名称=company.name,
                            股票代码=company.symbol,
                        )
                        for _, r3 in growth_df.iterrows():
                            growth_ranking_rows.append(
                                {
                                    "指标": r3["指标"],
                                    "最近期": r3["最近期"],
                                    "上期": r3["上期"],
                                    "板块名称": sector_name,
                                    "公司名称": company.name,
                                    "股票代码": company.symbol,
                                    "增长率(%)": r3["增长率(%)"],
                                    "最新值": r3["最新值"],
                                    "上期值": r3["上期值"],
                                }
                            )

            financial_wide = (
                pd.DataFrame(financial_rows)
                if financial_rows
                else pd.DataFrame(columns=["板块名称", "公司名称", "股票代码"])
            )

            # 4.2 收集主营业务（默认取第一行，字段加前缀“主营_”）
            for sector_name, (sector, companies_data, _) in all_sectors_data.items():
                for company in sector.companies:
                    if company.name not in companies_data:
                        continue
                    _, business_df, _, _ = companies_data[company.name]
                    if business_df is None or business_df.empty:
                        continue

                    row: Dict[str, Any] = {
                        "板块名称": sector_name,
                        "公司名称": company.name,
                        "股票代码": company.symbol,
                    }
                    first = business_df.iloc[0]
                    for col in business_df.columns:
                        if col not in ("公司名称", "股票代码"):
                            row[f"主营_{col}"] = first.get(col, None)
                    business_rows.append(row)

            business_flat = (
                pd.DataFrame(business_rows)
                if business_rows
                else pd.DataFrame(columns=["板块名称", "公司名称", "股票代码"])
            )

            # 4.3 合并财务与主营
            if financial_wide.empty and business_flat.empty:
                merged_all = pd.DataFrame({"信息": ["没有可用的公司财务/主营业务数据"]})
            else:
                keys = ["板块名称", "公司名称", "股票代码"]
                merged_all = pd.merge(
                    financial_wide, business_flat, on=keys, how="outer"
                )

            merged_all.to_excel(writer, sheet_name="所有公司汇总", index=False)
            sh = writer.book["所有公司汇总"]
            _adjust_sheet(sh)
            self._apply_fonts_to_sheet(sh)
            # 可选：冻结首行 + 简单自动列宽（失败也不影响导出）
            try:
                sheet = writer.book["所有公司汇总"]
                sheet.freeze_panes = "A2"
                for col in sheet.columns:
                    max_len = max(
                        (len(str(cell.value)) if cell.value is not None else 0)
                        for cell in col
                    )
                    col_letter = col[0].column_letter
                    sheet.column_dimensions[col_letter].width = min(max_len + 2, 60)
            except Exception:
                pass

            # 6) 全市场排行榜（按指标分别取 Top-N）
            def _topn_by_metric(
                df: pd.DataFrame, value_col: str, extra_cols: List[str]
            ) -> pd.DataFrame:
                frames: List[pd.DataFrame] = []
                for metric in sorted(df["指标"].dropna().unique().tolist()):
                    sub = (
                        df[df["指标"] == metric]
                        .sort_values(value_col, ascending=False)
                        .head(self.top_n)
                    )
                    sub = sub.reset_index(drop=True)
                    sub.insert(0, "排名", sub.index + 1)
                    frames.append(sub[["排名", "指标"] + extra_cols + [value_col]])
                return (
                    pd.concat(frames, ignore_index=True) if frames else pd.DataFrame()
                )

            latest_df_all = pd.DataFrame(latest_ranking_rows)
            if not latest_df_all.empty:
                latest_topn = _topn_by_metric(
                    latest_df_all, "数值", ["期间", "板块名称", "公司名称", "股票代码"]
                )
                latest_topn.to_excel(writer, sheet_name="排行榜_最新值", index=False)
                sh = writer.book["排行榜_最新值"]
                _adjust_sheet(sh)
                self._apply_fonts_to_sheet(sh)

            growth_df_all = pd.DataFrame(growth_ranking_rows)
            if not growth_df_all.empty:
                growth_topn = _topn_by_metric(
                    growth_df_all,
                    "增长率(%)",
                    [
                        "最近期",
                        "上期",
                        "板块名称",
                        "公司名称",
                        "股票代码",
                        "最新值",
                        "上期值",
                    ],
                )
                growth_topn.to_excel(writer, sheet_name="排行榜_增长率", index=False)
                sh = writer.book["排行榜_增长率"]
                _adjust_sheet(sh)
                self._apply_fonts_to_sheet(sh)

        return filepath
